package pe.com.bbva.configuracion.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import pe.com.bbva.configuracion.dao.CorreoDAO;
import pe.com.bbva.configuracion.domain.Correo;
import pe.com.bbva.mantenimiento.domain.Tabla;
import pe.com.bbva.util.Constantes;
import pe.com.stefanini.core.dao.GenericDAOImpl;
import pe.com.stefanini.core.exceptions.BOException;
import pe.com.stefanini.core.exceptions.DAOException;

@Service("correoDAO")
public class CorreoDAOImpl extends GenericDAOImpl<Correo> implements CorreoDAO{

	@Autowired
	public CorreoDAOImpl(SessionFactory sessionFactory) {
		super(sessionFactory);
	}
	
	@SuppressWarnings("unchecked")
	public List<Correo> findCorreos(Correo correo)throws BOException, DAOException {
		String where = "";
		if(correo.getDescripcion()!=null && !correo.getDescripcion().equals("")){
			where = where + (where==""?"":" AND ") + "upper(descripcion) like upper('%" + correo.getDescripcion()+ "%')";
		}
		if(correo.getIncidencia()!=null && !correo.getIncidencia().equals("")){
			where = where + (where==""?"":" AND ") + "incidencia = '" + correo.getIncidencia()+ "'";
		}
		if(correo.getTipoCorreo()!=null && !correo.getTipoCorreo().equals("")){
			where = where + (where==""?"":" AND ") + "tipoCorreo = '" + correo.getTipoCorreo()+ "'";
		}
		if(correo.getEstado()!=null && !correo.getEstado().equals("")){
			where = where + (where==""?"":" AND ") + "estado like '%" + correo.getEstado()+ "%'";
		}
		if(correo.getMailDestino()!=null && !correo.getMailDestino().equals("")){
			where = where + (where==""?"":" AND ") + "mailDestino like '%" + correo.getMailDestino()+ "%'";
		}
		if(correo.getMailCC()!=null && !correo.getMailCC().equals("")){
			where = where + (where==""?"":" AND ") + "mailCC like '%" + correo.getMailCC()+ "%'";
		}
		if(correo.getMailCCO()!=null && !correo.getMailCCO().equals("")){
			where = where + (where==""?"":" AND ") + "mailCCO like '%" + correo.getMailCCO()+ "%'";
		}
		if(correo.getId()!=null && !correo.getId().equals("")){
			where = where + (where==""?"":" AND ") + "id = '" + correo.getId()+ "'";
		}
		if(!where.equals("")){
			where = Constantes.WHERE_CLAUSE + where;
		}
		String orders = " order by fechaCreacion desc, descripcion";
		List<Correo> listaCorreos = super.executeQuery(Correo.class,where,orders);
		return listaCorreos;
	}
	
	@SuppressWarnings("deprecation")
	public Long ValidaCargaEnProceso() throws DAOException {
		Session hibernateSession = null;
		Connection con = null;
		CallableStatement cstmt =null;
		ResultSet rs  = null;		
		Long IdCarga=0L;
		try {
			hibernateSession = (Session) getHibernateTemplate().getSessionFactory().openSession();
			con = hibernateSession.connection();
			cstmt =con.prepareCall("{call TARGPS.TARGPSPKG.PR_TTARGPS_VALIDAR_ENPROCESO(?)}");			
			cstmt.registerOutParameter(1,oracle.jdbc.OracleTypes.VARCHAR);		
			cstmt.executeQuery();			
			IdCarga = Long.parseLong((String)cstmt.getObject(1));			
		} catch (HibernateException e) {
			e.printStackTrace();
			throw new DAOException(e.getMessage());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException(e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
			throw new DAOException(e.getMessage());
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
				if(cstmt!=null){
					cstmt.close();
				}
				hibernateSession.close();
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			} catch (HibernateException e) {
				e.printStackTrace();
				throw new DAOException(e.getMessage());
			} 
		}
		return IdCarga;
	}
	
	@SuppressWarnings("unchecked")
	public List<Correo> findCorreosByProceso() throws BOException, DAOException {
		String filtro = " where tipoCorreo IN ('1', '2')";
		List<Correo> listaCorreoInit = super.executeQuery(Correo.class, filtro, null);
		List<Correo> listaCorreo = new ArrayList<Correo>();
		for(Correo correo:listaCorreoInit){
			String where = " where upper(abreviado) like upper('TC -%') and valor = '" + correo.getTipoCorreo() + "'";
			List<Tabla> tabla = super.executeQuery(Tabla.class, where, null);
			correo.setTipoCorreo(tabla.get(0).getDescripcion());
			listaCorreo.add(correo);
		}
		return listaCorreo;
	}
	
	@SuppressWarnings("unchecked")
	public Correo findCorreoIncidencia(Long incidencia)throws BOException, DAOException {
		String where = " where incidencia = '" + incidencia +"')";
		String orders = " order by fechaCreacion desc, descripcion";
		List<Correo> listaCorreos = super.executeQuery(Correo.class,where,orders);
		Correo correo = new Correo();
		if(!listaCorreos.isEmpty() && listaCorreos.size()==1){
			correo = listaCorreos.get(0);
		}
		return correo;
	}
}